package com.pineapple.module.core.report.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.Map;

import com.pineapple.module.core.report.domain.Evolution;
import com.pineapple.module.core.report.domain.EvolutionCriteria;

public class EvolutionDAO {
	protected Evolution search(Connection conn,EvolutionCriteria evoCri) throws Exception{


		StringBuilder sql = new StringBuilder();
		sql.append(" select   DISTINCT(area_name),a.area_id,a.area_status,p.planting_status    ");
		sql.append(" from  area  a ");
		sql.append(" INNER JOIN  Planting p ON  a.area_id = p.area_id ");
		sql.append(" WHERE a.area_id = "+evoCri.getAreaId());
		System.out.println(sql.toString());
		Evolution evox = new Evolution();
		try(Statement stmt = conn.createStatement()){
			ResultSet rst = stmt.executeQuery(sql.toString());
			if (rst.next()) {
				evox.setAreaName(rst.getString("area_name"));
				evox.setPlaningStatus(rst.getString("planting_status"));
			}else{
				evox.setAreaName("");
				evox.setPlaningStatus("0");
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return evox;
	}

	protected Map<String,String> searchComboPlantAndArea(Connection conn) throws Exception{
		StringBuilder sql = new StringBuilder();
		Map<String,String> lstArea = new LinkedHashMap<String, String>();
		sql.append("SELECT a.area_name,p.planting_id,a.area_id ");
		sql.append("FROM planting p RIGHT JOIN area a ON p.area_id = a.area_id ");
		//sql.append("WHERE p.planting_status <> 0 ");

		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
		        while(rs.next()) {
		        	lstArea.put(rs.getString("area_id"), rs.getString("area_name"));
		        }
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstArea;
	}
}
